select
  c.source,
  c.conn_id,
  c.from_stop_idu as from_stop_idu,
  c.from_stop_sidu as from_stop_sidu,
  c.from_stop_time,
  c.to_stop_idu as to_stop_idu,
  c.to_stop_sidu as to_stop_sidu,
  c.to_stop_time,
  s1.stop_lat  as from_stop_lat,
  s1.stop_lon  as from_stop_lon,
  s1.stop_id   as from_stop_id,
  s1.stop_name as from_stop_name,
  s2.stop_lat  as to_stop_lat,
  s2.stop_lon  as to_stop_lon,
  s2.stop_id   as to_stop_id,
  s2.stop_name as to_stop_name,
  t.route_id,
  t.trip_id,
  t.trip_headsign,
  t.direction_id,
  r.route_short_name,
  r.route_long_name
from _cvconnd c
join stops s1 on c.from_stop_id = s1.stop_id and c.source = s1.source
join stops s2 on c.to_stop_id = s2.stop_id and c.source = s2.source
join trips t on c.trip_id= t.trip_id and c.source = t.source
join routes r on t.route_id = r.route_id and t.source = r.source
